<?php

namespace App\Services;

use App\Common\Enums\ConvertCallbackStatusEnum;
use App\Common\Enums\ConvertTypeEnum;
use App\Common\Services\BaseService;
use App\Models\PromotionConvertModel;

class PromotionConvertService extends BaseService
{
    /**
     * @param $option
     * @return bool|int
     * 统计
     */
    public function stat($option){
        $date = $option['date'];

        $startTime = "{$date} 00:00:00";
        $endTime = "{$date} 23:59:59";

        $datetime = date('Y-m-d H:i:s', TIMESTAMP);

        $costMap = [];
        $items = $this->getPromotionCostItems($startTime, $endTime);
        foreach($items as $item){
            $costMap[$item['promotion_id']] = $item['cost'];
        }

        $map = [];
        $items = $this->getConvertCallbackItems($startTime, $endTime);
        foreach($items as $item){
            if(empty($item['pid'])){
                continue;
            }

            // 出价
            if(empty($item['bid']) && !empty($item['project_cpa_bid'])){
                $item['bid'] = $item['project_cpa_bid'];
            }

            if(!isset($map[$item['promotion_id']])){
                $map[$item['promotion_id']] = [
                    'stat_date' => $date,
                    'project_id' => $item['project_id'],
                    'promotion_id' => $item['promotion_id'],
                    'bid' => $item['bid'] ?? 0,
                    'convert_callback_strategy_id' => $item['convert_callback_strategy_id'] ?? 0,
                    'convert_callback_strategy_group_id' => $item['convert_callback_strategy_group_id'] ?? 0,
                    'cost' => $costMap[$item['promotion_id']] ?? 0,
                    'convert_callback_count' => 0,
                    'no_convert_callback_count' => 0,
                    'created_at' => $datetime,
                    'updated_at' => $datetime,
                ];
            }

            if(in_array($item['convert_callback_status'], [
                ConvertCallbackStatusEnum::WAITING_CALLBACK,
                ConvertCallbackStatusEnum::MACHINE_CALLBACK,
                ConvertCallbackStatusEnum::ROI_MACHINE_CALLBACK,
                ConvertCallbackStatusEnum::MANUAL_CALLBACK,
            ])){
                $map[$item['promotion_id']]['convert_callback_count'] += 1;
            }elseif($item['convert_callback_status'] == ConvertCallbackStatusEnum::DOT_NEED_CALLBACK_BY_PROMOTION_CONVERT_COST){
                $map[$item['promotion_id']]['no_convert_callback_count'] += 1;
            }
        }

        $data = [];
        foreach($map as $item){
            $item['convert_cost'] = $item['convert_callback_count'] > 0 ? $item['cost'] / $item['convert_callback_count'] : 0;
            $totalConvertCallbackCount = $item['convert_callback_count'] + $item['no_convert_callback_count'];
            $item['real_convert_cost'] = $totalConvertCallbackCount > 0 ? $item['cost'] / $totalConvertCallbackCount : 0;
            $data[] = $item;
        }

        $promotionConvertStatModel = new PromotionConvertModel();
        $ret = $promotionConvertStatModel->batchInsertOrUpdate($data);

        return $ret;
    }

    /**
     * @param $startTime
     * @param $endTime
     * @return array
     * 获取转化回传
     */
    public function getConvertCallbackItems($startTime, $endTime){
        #TODO:添加转化日期字段索引
        $convertType = ConvertTypeEnum::PAY;
        $sql = "
            SELECT
                cc.`convert_callback_status` `convert_callback_status`,
                c.`project_id` `project_id`, c.`promotion_id` `promotion_id`, c.`channel_id` `channel_id`,
                op.`id` pid, op.`cpa_bid` `bid`, opj.`cpa_bid` `project_cpa_bid`,
                ope.`convert_callback_strategy_id` `convert_callback_strategy_id`,
                ope.`convert_callback_strategy_group_id` `convert_callback_strategy_group_id`
            FROM
                n8_adv_ocean_v2.convert_callbacks cc
            LEFT JOIN n8_adv_ocean_v2.clicks c ON cc.click_id = c.id
            LEFT JOIN n8_adv_ocean_v2.ocean_promotions op ON c.promotion_id = op.id
            LEFT JOIN n8_adv_ocean_v2.ocean_projects opj ON opj.id = op.project_id
            LEFT JOIN n8_adv_ocean_v2.ocean_promotion_extends ope ON op.id = ope.promotion_id
            WHERE convert_type = '{$convertType}'
                AND convert_at BETWEEN '{$startTime}' AND '{$endTime}'
        ";
        return $this->execSourceSql($sql);
    }

    /**
     * @param $startTime
     * @param $endTime
     * @return array
     * 获取广告消耗
     */
    public function getPromotionCostItems($startTime, $endTime){
        $sql = "
            SELECT 
                promotion_id, SUM(stat_cost) cost
            FROM n8_adv_ocean_v2.ocean_promotion_reports
            WHERE stat_datetime BETWEEN '{$startTime}' AND '{$endTime}'
            GROUP BY promotion_id
        ";
        return $this->execSourceSql($sql);
    }

    /**
     * @param $date
     * @param $promotionId
     * @return mixed
     * 获取广告转化
     */
    public function getPromotionConvertItem($date, $promotionId){
        $promotionConvertModel = new PromotionConvertModel();
        $promotionConvert = $promotionConvertModel->where('stat_date', $date)
            ->where('promotion_id', $promotionId)
            ->first();

        return $promotionConvert;
    }

    /**
     * @param $promotionId
     * @return |null
     * 获取广告转化统计
     */
    public function getPromotionConvertStat($promotionId){
        $sql = "
            SELECT 
                `promotion_id`, MAX(`bid`) `bid`, SUM(`convert_callback_count`) `convert_callback_count`,
                SUM(`no_convert_callback_count`) `no_convert_callback_count`, SUM(`cost`) `cost`
            FROM n8_adv_ocean_v2.promotion_converts
            WHERE promotion_id = '{$promotionId}'
            GROUP BY promotion_id
        ";
        $items = $this->execSql($sql);

        $item = null;
        if(!empty($items[0])){
            $item = $items[0];
            $item['convert_cost'] = $item['convert_callback_count'] > 0 ? $item['cost'] / $item['convert_callback_count'] : 0;
            $totalConvertCallbackCount = $item['convert_callback_count'] + $item['no_convert_callback_count'];
            $item['real_convert_cost'] = $totalConvertCallbackCount > 0 ? $item['cost'] / $totalConvertCallbackCount : 0;
        }

        return $item;
    }
}
